﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dados.Interface;
using MySql.Data.MySqlClient;
using System.Data;

namespace Dados
{
    public class ConexaoMYSQL : IConexao
    {
        
        // banco hopesdado em http://72.29.72.39:2082/logout usuario:didizim  senha:20122012

        private string strConn = @"Persist Security Info=False;server=72.29.72.39;Database=didizim_cotfacil;Uid=didizim_adm;Pwd=@dmr00t1";

        MySqlConnection objConn = null;
        
        MySqlTransaction tx = null;

        public bool conectar()
            {
                objConn = new MySqlConnection(strConn);
                
                try
                {
                    objConn.Open();
                }
                catch
                {
                    return false;
                }
                tx = objConn.BeginTransaction();
                return true;
            }

        public bool desconectar()
            {
                try
                {
                    if (objConn.State != ConnectionState.Closed)
                    {
                        objConn.Close();
                        objConn.Dispose();
                    }
                }
                catch
                {
                    return false;
                }

                return true;
            }

        public DataTable retornarTabela(string p_strSql, List<MySqlParameter> p_obParams, string p_strNmTabelaRetorno)
            {
                if (!this.conectar())
                {
                    return null;
                }

                MySqlCommand objCmd = new MySqlCommand(p_strSql, objConn);
                //objCmd.Transaction = tx;

                foreach (MySqlParameter param in p_obParams)
                {
                    objCmd.Parameters.Add(param);
                }

                MySqlDataAdapter objAdp = new MySqlDataAdapter(objCmd);
                DataSet ds = new DataSet();

                try
                {
                    objAdp.Fill(ds, p_strNmTabelaRetorno);
                }
                catch
                {
                    //tx.Rollback();
                    return null;
                }

                //tx.Commit();
                this.desconectar();

                return ds.Tables[p_strNmTabelaRetorno];
            }

        public bool executarComando(string p_strSql, List<MySqlParameter> p_obParams)
            {
                bool blnResult = false;
                if (!this.conectar())
                {
                    return false;
                }

                MySqlCommand objCmd = new MySqlCommand(p_strSql, objConn);

                objCmd.Transaction = tx;

                foreach (MySqlParameter param in p_obParams)
                {
                    objCmd.Parameters.Add(param);
                }
                
                try
                {
                    blnResult = (objCmd.ExecuteNonQuery() > 0 ? true : false);
                }
                catch
                {
                    tx.Rollback();
                    blnResult = false;
                }

                tx.Commit();
                
                this.desconectar();

                return blnResult;

            }
     
    }
}
